﻿/*----------------------------------------------------------------
// Copyright (C) 2011 上海
// 版权所有。 
//
// 文件名：DataAccessCashRegister
// 文件功能描述：现金出纳表数据访问层
//
// 
// 创建标识：2012-06-12 王冲
//
// 修改标识：
// 修改描述：
//
//
//----------------------------------------------------------------*/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DbAdapter.sqldbAdapter;
using System.Data;
using System.Data.SqlClient;

namespace DAO.WaiGaoQiao
{
    public class DataAccessCashRegister
    {
        /// <summary>
        /// 获取现金出纳表
        /// </summary>
        /// <returns></returns>
        public DataSet BindList(int pagesize, int startindex, object[] obj, string tablename, out int count)
        {
            /*
             * 先调用sp_CreateTabelForDeposith存储过程创建一个表名为tablename的表,用来存储汇总数据
             */
            sqldb db = new sqldb();
            SqlParameter[] parameters = {
                                            new SqlParameter("@TEMPTABLENAME", SqlDbType.VarChar, 30)
                                        };
            parameters[0].Value = tablename;
            db.ExecuteNonQuery(GetConntionString.conntionstring(), CommandType.StoredProcedure, "sp_CreateTabelForDeposith", parameters);

            /*
             * 再从开始日期循环到结束日期，调用SP_CONREPDEPOSITFORDATE存储过程向刚才新建的表里面逐一的分析出数据添加到表中,每天一条。
             */
            string yy = obj[0].ToString();
            string mm = obj[1].ToString();
            string yy1 = yy;
            string mm1 = (Convert.ToInt32(mm) - 1).ToString();
            if (Convert.ToInt32(mm) == 1)
            {
                yy1 = (Convert.ToInt32(yy) - 1).ToString();
                mm1 = "12";
            }
            string begin = yy1 + "-" + mm1 + "-" + "21";
            string end = yy + "-" + mm + "-" + "20";
            for (DateTime de = Convert.ToDateTime(begin); de <= Convert.ToDateTime(end); de = de.AddDays(1))
            {
                SqlParameter[] parameters1 = {
                                            new SqlParameter("@TEMPTABLENAME", SqlDbType.VarChar, 30),
                                            new SqlParameter("@CURDATA", SqlDbType.DateTime),
                                            new SqlParameter("@DEPTID", SqlDbType.VarChar, 5000),
                                            new SqlParameter("@PERSONELID", SqlDbType.VarChar, 20),
                                            new SqlParameter("@PERSONNELTYPEID", SqlDbType.VarChar, 20),
                                            new SqlParameter("@NAME", SqlDbType.VarChar, 20),
                                            new SqlParameter("@NODEID", SqlDbType.VarChar, 50),
                                            new SqlParameter("@MACNO", SqlDbType.VarChar, 100),
                                            new SqlParameter("@DepositName", SqlDbType.VarChar, 20)
                                        };
                parameters1[0].Value = tablename;
                parameters1[1].Value = de;
                parameters1[2].Value = "%";
                parameters1[3].Value = "%";
                parameters1[4].Value = "%";
                parameters1[5].Value = "%";
                parameters1[6].Value = "%";
                parameters1[7].Value = "%";
                parameters1[8].Value = "%";
                db.ExecuteNonQuery(GetConntionString.conntionstring(), CommandType.StoredProcedure, "SP_CONREPDEPOSITFORDATE", parameters1);
            }
            /*
             * 获取刚创建的表中的记录数并用out返回用于分页
             */
            string query = "select count(1) from " + tablename;
            object rowcount = db.ExecuteScalar(GetConntionString.conntionstring(), CommandType.Text, query);
            count = Convert.ToInt32(rowcount);

            /*
             * 从刚才创建的表中取出汇总数据，然后分页，根据日期排序
             */
            int startrow = pagesize * (startindex - 1);
            int endrow = pagesize * startindex + 1;
            query = "select * from " + tablename + " where 1=1";
            DataSet ds = db.ExecuteDataset(GetConntionString.conntionstring(), CommandType.Text, query);
            return ds;
        }


        /// <summary>
        /// 获取现金出纳表
        /// </summary>
        /// <returns></returns>
        public DataSet GetListAll(object[] obj, string tablename)
        {
            /*
             * 先调用sp_CreateTabelForDeposith存储过程创建一个表名为tablename的表,用来存储汇总数据
             */
            sqldb db = new sqldb();
            SqlParameter[] parameters = {
                                            new SqlParameter("@TEMPTABLENAME", SqlDbType.VarChar, 30)
                                        };
            parameters[0].Value = tablename;
            db.ExecuteNonQuery(GetConntionString.conntionstring(), CommandType.StoredProcedure, "sp_CreateTabelForDeposith", parameters);

            /*
             * 再从开始日期循环到结束日期，调用SP_CONREPDEPOSITFORDATE存储过程向刚才新建的表里面逐一的分析出数据添加到表中,每天一条。
             */
            string yy = obj[0].ToString();
            string mm = obj[1].ToString();
            string yy1 = yy;
            string mm1 = (Convert.ToInt32(mm) - 1).ToString();
            if (Convert.ToInt32(mm) == 1)
            {
                yy1 = (Convert.ToInt32(yy) - 1).ToString();
                mm1 = "12";
            }
            string begin = yy1 + "-" + mm1 + "-" + "21";
            string end = yy + "-" + mm + "-" + "20";
            for (DateTime de = Convert.ToDateTime(begin); de <= Convert.ToDateTime(end); de = de.AddDays(1))
            {
                SqlParameter[] parameters1 = {
                                            new SqlParameter("@TEMPTABLENAME", SqlDbType.VarChar, 30),
                                            new SqlParameter("@CURDATA", SqlDbType.DateTime),
                                            new SqlParameter("@DEPTID", SqlDbType.VarChar, 5000),
                                            new SqlParameter("@PERSONELID", SqlDbType.VarChar, 20),
                                            new SqlParameter("@PERSONNELTYPEID", SqlDbType.VarChar, 20),
                                            new SqlParameter("@NAME", SqlDbType.VarChar, 20),
                                            new SqlParameter("@NODEID", SqlDbType.VarChar, 50),
                                            new SqlParameter("@MACNO", SqlDbType.VarChar, 100),
                                            new SqlParameter("@DepositName", SqlDbType.VarChar, 20)
                                        };
                parameters1[0].Value = tablename;
                parameters1[1].Value = de;
                parameters1[2].Value = "%";
                parameters1[3].Value = "%";
                parameters1[4].Value = "%";
                parameters1[5].Value = "%";
                parameters1[6].Value = "%";
                parameters1[7].Value = "%";
                parameters1[8].Value = "%";
                db.ExecuteNonQuery(GetConntionString.conntionstring(), CommandType.StoredProcedure, "SP_CONREPDEPOSITFORDATE", parameters1);
            }
            /*
             * 从刚才创建的表中取出汇总数据，然后分页，根据日期排序
             */
            string query = "select * from " + tablename + " where 1=1";
            DataSet ds = db.ExecuteDataset(GetConntionString.conntionstring(), CommandType.Text, query);
            return ds;
        }
    }
}
